package com.y.report.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;

/**
 * 封装jdbc
 */
public class DbTools {
    private static Logger log = LoggerFactory.getLogger(DbTools.class);
    private static ThreadLocal<Connection> local = new ThreadLocal<>();
    private static Properties jdbcProp;

    private static final String JDBC_PROP_FILE_NAME = "jdbc.properties";
    private static final String JDBC_URL_KEY = "url";
    private static final String JDBC_DRIVER_KEY = "driverClassName";
    private static final String JDBC_USER_NAME_KEY = "user";
    private static final String JDBC_PASSWORD_KEY = "password";

    static {
        try {
            InputStream is = DbTools.class.getClassLoader().getResourceAsStream(JDBC_PROP_FILE_NAME);
            jdbcProp = new Properties();
            jdbcProp.load(is);
        } catch (IOException e) {
            log.warn("load cfg file err. file:{}", JDBC_PROP_FILE_NAME, e);
            throw new RuntimeException(e);
        }
        Arrays.asList(JDBC_URL_KEY, JDBC_DRIVER_KEY, JDBC_USER_NAME_KEY, JDBC_PASSWORD_KEY)
                .stream()
                .forEach(k -> {
                    String v = jdbcProp.getProperty(k, null);
                    System.out.println(String.format("read prop file:[%s], key:[%s] -> v:[%s]", JDBC_PROP_FILE_NAME, k, v));
                });
        try {
            Class.forName(jdbcProp.getProperty(JDBC_DRIVER_KEY));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() {
        Connection conn = local.get();
        if (conn == null) {
            try {
                conn = DriverManager.getConnection(jdbcProp.getProperty(JDBC_URL_KEY), jdbcProp.getProperty(JDBC_USER_NAME_KEY), jdbcProp.getProperty(JDBC_PASSWORD_KEY));
                local.set(conn);
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        return conn;
    }

    public static void closeConn(Connection conn, Statement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        conn = conn != null ? conn : local.get();
        if (conn != null) {
            try {
                conn.close();
                local.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 基本查询操作
     *
     * @param sql   SQL语句
     * @param clazz 类类型
     * @param args  查询参数
     * @return
     */
    public <T> List<T> query(String sql, Class<T> clazz, Object... args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List list = null;
        //数据库操作
        try {
            //通过数据库连接池-获取Connectio连接
            connection = getConn();
            //获取Statement
            statement = connection.prepareStatement(sql);
            //参数设置
            for (int i = 0; args != null && i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            //执行CURD操作，获取ResultSet
            resultSet = statement.executeQuery();
            //实例化List
            list = new ArrayList<>();
            if (clazz == null) {
                //获取结果集对象
                ResultSetMetaData metaData = resultSet.getMetaData();
                //获取总列数
                int count = metaData.getColumnCount();
                //遍历结果
                while (resultSet.next()) {
                    //一行结果  转换成map
                    Map map = new HashMap();
                    //有多少列取多少个结果
                    for (int i = 1; i <= count; i++) {
                        //获取列名
                        String name = metaData.getColumnLabel(i);
                        map.put(name, resultSet.getObject(name));
                    }
                    //存储当前行数据
                    list.add(map);
                }
            } else {
                //利用反射获取字段
                Field[] declaredFields = clazz.getDeclaredFields();
                //解析ResultSet
                while (resultSet.next()) {
                    Object object = clazz.newInstance();
                    for (Field declaredField : declaredFields) {
                        if (Modifier.isStatic(declaredField.getModifiers()))
                            continue;
                        declaredField.setAccessible(true);
                        String fieldName = declaredField.getName();
                        //通过反射获取setter方法
                        String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                        Method setMethod = clazz.getDeclaredMethod(setMethodName, declaredField.getType());
                        //调用setter方法
                        setMethod.invoke(object, resultSet.getObject(fieldName));
                        declaredField.setAccessible(false);
                    }
                    //添加到列表
                    list.add(object);
                }
            }
            return list;
        } catch (SQLException | InvocationTargetException | NoSuchMethodException | IllegalAccessException |
                 InstantiationException e) {
            e.printStackTrace();
            return list;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //归还数据库连接对象
            closeConn(connection, statement, resultSet);
        }
    }


    /**
     * 更新操作（添加/删除/修改）
     *
     * @param sql
     * @param args
     * @return
     */
    public int update(String sql, Object... args) throws SQLException {
        Connection connection = getConn();
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置?的值
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);//?的编号从1开始，不是从0开始，数组的下标是从0开始
            }
        }
        //执行sql
        int len = ps.executeUpdate();
        ps.close();
        //这里检查下是否开启事务,开启不关闭连接,业务方法关闭!
        //没有开启事务的话,直接回收关闭即可!
        if (connection.getAutoCommit()) {
            //回收
            closeConn(connection, ps, null);
        }
        return len;
    }

}